import pandas as pd
import os
import json

def debug_workload_data():
    """全面调试工作量数据"""
    workload_dir = 'uploads/workload'
    
    if not os.path.exists(workload_dir):
        print("❌ 工作量目录不存在")
        return
    
    files = [f for f in os.listdir(workload_dir) if f.endswith(('.xls', '.xlsx'))]
    if not files:
        print("❌ 未找到工作量文件")
        return
    
    filepath = os.path.join(workload_dir, files[0])
    print(f"📁 正在调试文件: {filepath}")
    
    try:
        # 读取文件
        if filepath.endswith('.xls'):
            df = pd.read_excel(filepath, engine='xlrd')
        else:
            df = pd.read_excel(filepath, engine='openpyxl')
        
        print(f"✅ 文件读取成功，形状: {df.shape}")
        
        # 显示完整列信息
        print("\n📊 列名详细信息:")
        for i, col in enumerate(df.columns):
            clean_col = str(col).strip()
            print(f"  {i}: '{col}' -> '{clean_col}' (类型: {type(col).__name__})")
        
        # 标准化列名
        df.columns = df.columns.str.strip()
        
        # 检查数据类型和空值
        print("\n🔍 数据类型分析:")
        for col in df.columns:
            non_null_count = df[col].notna().sum()
            null_count = df[col].isna().sum()
            print(f"  {col}: {df[col].dtype}, 非空: {non_null_count}, 空值: {null_count}")
        
        # 显示数据样本
        print("\n📋 前3行数据:")
        print(df.head(3).to_string())
        
        # 智能列名匹配测试
        column_mapping = {
            '人员名称': ['人员名称', '用户名称', '姓名', '成员名称', '人员姓名', 'name', 'Name'],
            '饱和度': ['饱和度', '工作饱和度', '负载率', '饱和度(%)', 'saturation', 'Saturation'],
            '代码当量': ['代码当量', '代码量', '代码贡献', '当量', 'code_equivalent', 'Code'],
            '总工时': ['总工时', '工时', '工作时长', '总时长(小时)', 'hours', 'Hours', '总时长'],
            'AI活跃天数': ['AI活跃天数', 'AI使用天数', '活跃天数', 'AI天数', 'ai_days', 'AI Days']
        }
        
        print("\n🎯 列名匹配结果:")
        matched_columns = {}
        for standard_name, possible_names in column_mapping.items():
            matched_col = None
            for col_name in possible_names:
                if col_name in df.columns:
                    matched_col = col_name
                    break
            matched_columns[standard_name] = matched_col
            status = "✅" if matched_col else "❌"
            print(f"  {standard_name}: {matched_col or '未找到'} {status}")
        
        # 解析测试
        print("\n🧪 解析测试结果:")
        test_records = []
        for _, row in df.head(3).iterrows():
            record = {}
            for standard_name, possible_names in column_mapping.items():
                for col_name in possible_names:
                    if col_name in df.columns and pd.notna(row[col_name]):
                        value = row[col_name]
                        try:
                            if standard_name == '饱和度':
                                if isinstance(value, str) and '%' in value:
                                    record[standard_name] = float(value.replace('%', ''))
                                elif pd.isna(value) or str(value).strip() == '':
                                    record[standard_name] = 0
                                else:
                                    record[standard_name] = float(value)
                            elif standard_name in ['总工时', '代码当量']:
                                record[standard_name] = float(value) if pd.notna(value) else 0
                            elif standard_name == 'AI活跃天数':
                                record[standard_name] = int(value) if pd.notna(value) else 0
                            else:
                                record[standard_name] = str(value)
                            break
                        except (ValueError, TypeError) as e:
                            print(f"    ⚠️ 转换失败 {col_name}={value}: {e}")
                            record[standard_name] = 0
                    else:
                        record[standard_name] = 0
            
            # 确保默认值
            record.setdefault('人员名称', '未知人员')
            record.setdefault('饱和度', 0)
            record.setdefault('代码当量', 0)
            record.setdefault('总工时', 0)
            record.setdefault('AI活跃天数', 0)
            
            test_records.append(record)
            print(f"    📊 记录: {record}")
        
        # 保存调试结果
        debug_result = {
            'file_info': {
                'path': filepath,
                'shape': df.shape,
                'columns': list(df.columns)
            },
            'matched_columns': matched_columns,
            'sample_data': test_records,
            'data_types': {col: str(dtype) for col, dtype in df.dtypes.items()}
        }
        
        with open('debug_result.json', 'w', encoding='utf-8') as f:
            json.dump(debug_result, f, ensure_ascii=False, indent=2)
        
        print(f"\n💾 调试结果已保存到 debug_result.json")
        
    except Exception as e:
        print(f"❌ 读取文件失败: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    debug_workload_data()